import ftclient
from authorization.clientlogin import ClientLogin
from sql.sqlbuilder import SQL
import StringIO, csv, urllib
import logging

USERNAME = 'osu.map@gmail.com'
PASSWORD = 'dlzosumap'
TABLE_LIST = None
DLZ_PROJECT_LIST = 'dlz_project_list'
DLZ_INVOICE_LIST = 'dlz_invoice_list'
DLZ_COMPACTION_JOIN = 'compaction_join'
DLZ_IFORM_COMPACTION_PROJECT = 'compaction_project'

FT_CONFIG = {}

def get_config():
    '''get the configuration of the project'''
    table_list = getTableList()
    ft_client = getFtClient(USERNAME, PASSWORD)
    table_id = getTableId('dlz_ft_config')
    sql = "select * from %s" % table_id
    response = processResponse(ft_client.query(sql))
    for r in response:
        table_name = r['table_name']
        FT_CONFIG[table_name] = table_list

def download_invoice(project_number, invoice_from, invoice_to, filename):
    ft_client = getFtClient(USERNAME, PASSWORD)
    table_id = getTableId('compaction_join')
    fields = 'check_curves    comments    comments    compaction    CREATED_BY    CREATED_DATE    CREATED_LOCATION    curve_no    data_rivewed    date_    dry_unit_weight    dry_unit_weight_field    dry_unit_weight_lab    ds1    ds2    enter_lab_mositure_data    factor    ID    lat    lon    max_dry_unit_weight    model    moisture_contentfield    moisture_contentlab    moisture_remarks    mold    ms    nuclear_no    obtained    one_point_data    one_point_moisture    optimum_moisture_content    project_name    remarks    soil_and_mold    soil_unit_weight    specified    test_interval    test_location    test_no    tested_by    wet_unit_weight'.split()
    sql = "select ROWID, %s from %s where date_<= '%s' and date_>='%s' and project_name starts with '%s' order by CREATED_DATE" % (','.join(fields),table_id, invoice_to, invoice_from, project_number)
    logging.info(sql)
    response = processResponse(ft_client.query(sql))
    header = 'report_test_no  test_no   date_    test_location    test_interval    wet_unit_weight    moisture_contentfield    dry_unit_weight    curve_no    max_dry_unit_weight    optimum_moisture_content    specified    obtained    remarks    project_name    check_curves    comments    compaction    CREATED_BY    CREATED_DATE    CREATED_LOCATION    data_rivewed    dry_unit_weight_field    dry_unit_weight_lab    ds1    ds2    enter_lab_mositure_data    factor    ID    lat    lon    model    moisture_contentlab    moisture_remarks    mold    ms    nuclear_no    one_point_data    one_point_moisture    rowid    soil_and_mold    soil_unit_weight    tested_by'.split()
    import csv, StringIO
    out = StringIO.StringIO()
    writer = csv.writer(out)
    writer.writerow(header)
    for record in response:
        row = []
        for fld in header:
            if fld=='report_test_no':
                value = ''
            else:
                value = record[fld]
            if fld in ['test_interval', 'test_location', 'remarks']:
                value = value.title()
                value = ' '.join(value.split())
            else:
                try:
                    value = int(value)
                except:
                    try:
                        value = '%.1f' % float(value)
                    except:
                        value = value
            row.append(value)
        writer.writerow(row)
    results = out.getvalue()
    out.close()
    return results
    
def getFields(tableid):
    '''return the schema of a table'''
    ft_client = getFtClient(USERNAME, PASSWORD)
    response = processResponse(ft_client.query(SQL().describeTable(tableid)))
    results = []
    TYPES = {'string': 'String', 'number': 'Float', 'datetime':'date', 'location': 'String'}
    for r in response:
        if True: #r['name'][0].islower():
            results.append({'name': r['name'], 
                            'type': TYPES[r['type']],
                            'header': r['name'],
                            'dataIndex': r['name']})
    return results

def styleText(text):
    return text.replace('_', ' ').title()

def insert(tableid, record):
    '''insert a record into a table'''
    ft_client = getFtClient(USERNAME, PASSWORD)
    logging.info(SQL().insert(tableid, record))
    return int(ft_client.query(SQL().insert(tableid, record)).split("\n")[1])

def delete(tableid, rowid):
    '''insert a record into a table'''
    ft_client = getFtClient(USERNAME, PASSWORD)
    ft_client.query(SQL().delete(tableid, rowid))

def update(tableid, rowid, record):
    '''insert a record into a table'''
    ft_client = getFtClient(USERNAME, PASSWORD)
    logging.info(SQL().update(tableid, record.keys(), record.values(), rowid))
    ft_client.query(SQL().update(tableid, record.keys(), record.values(), rowid))    
def getTableList():
    ft_client = getFtClient(USERNAME, PASSWORD)
    return processResponse(ft_client.query(SQL().showTables()))
    
def getTableId(tablename):
    '''return the table id based on its name'''
    
    for table in getTableList():
        if table['name']==tablename:
            return table['table id']
        
def getFields(tableid):
    '''return the schema of a table'''
    ft_client = getFtClient(USERNAME, PASSWORD)
    try: 
        tableid = int(tableid)
    except:
        tableid = int(getTableId(tableid))
    response = processResponse(ft_client.query(SQL().describeTable(tableid)))
    return response


def getFtClient(username, password):
    '''get the Fusion Table Client for query'''
    token = ClientLogin().authorize(username, password)
    ft_client = ftclient.ClientLoginFTClient(token)
    return ft_client

def getMapping():
    MAPPING_TABLE = 'mapping'
    ft_client = getFtClient(USERNAME, PASSWORD)
    tables = processResponse(ft_client.query(SQL().showTables()))
    ids = [x['table id'] for x in tables if x['name']=='mapping']
    if len(ids)==1:
        tableid = ids[0]
        return processResponse(ft_client.query("select * from %s" % tableid))
    
def processResponse(text):
    '''process the fusion table query results into a list of dictionary of records'''
    import csv, StringIO
    t = StringIO.StringIO(text)
    reader = csv.reader(t)
    results = []
    h = reader.next()
    
    for l in reader:
        results.append(dict(zip(h,l)))
    t.close()
    return results